﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Chen.CefWebKit
{
    class SqliteHelper
    {
        SQLiteConnection conn;
        public SqliteHelper(string FilePath)
        {
            conn = new SQLiteConnection("Data Source=" + FilePath + ";Pooling=true");
        }
        public SQLiteConnection sqlconn()
        {
            //SqlConnection conn = new SqlConnection("SERVER=192.168.1.150;DATABASE=ChinaDigitalLibrary;PWD=JingHe0713;UID=sa;");
            return conn;
        }
        public DataTable select(string sqlstr)
        {
            SQLiteConnection conn = sqlconn();
            conn.Open();
            try
            {
                SQLiteCommand comm = new SQLiteCommand(sqlstr, conn);
                DataSet ds = new DataSet();
                SQLiteDataAdapter adp = new SQLiteDataAdapter(comm);
                adp.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                Thread.Sleep(5000);
                return select(sqlstr);
            }

        }
        public DataTable select(string sql, Dictionary<string, string> strlist)
        {
            SQLiteConnection conn = sqlconn();
            conn.Open();
            try
            {
                SQLiteCommand comm = new SQLiteCommand(sql, conn);
                foreach (var item in strlist)
                {
                    if (item.Value == string.Empty || item.Value == null)
                    {
                        comm.Parameters.AddWithValue(item.Key, DBNull.Value);
                    }
                    else
                    {
                        comm.Parameters.AddWithValue(item.Key, item.Value);
                    }

                }
                DataSet ds = new DataSet();
                SQLiteDataAdapter adp = new SQLiteDataAdapter(comm);
                adp.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch
            {
                conn.Close();
                Thread.Sleep(5000);
                return select(sql, strlist);
            }
        }
        public bool add(string sqlstr, string paramname = "", string param = "")
        {
            SQLiteConnection conn = sqlconn();
            conn.Open();
            try
            {
                SQLiteCommand comm = new SQLiteCommand(sqlstr, conn);
                if (paramname != string.Empty)
                {
                    comm.Parameters.AddWithValue(paramname, param);
                }
                comm.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return add(sqlstr, paramname, param);
            }
        }
        public bool paramadd(string sqlstr, Dictionary<string, string> strlist)
        {
            SQLiteConnection conn = sqlconn();
            conn.Open();
            try
            {
                SQLiteCommand comm = new SQLiteCommand(sqlstr, conn);
                if (strlist.Count() > 0)
                {
                    foreach (var item in strlist)
                    {
                        if (item.Value == string.Empty || item.Value == null)
                        {
                            comm.Parameters.AddWithValue(item.Key, DBNull.Value);
                        }
                        else
                        {
                            comm.Parameters.AddWithValue(item.Key, item.Value);
                        }

                    }
                }
                comm.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                return paramadd(sqlstr, strlist);
            }
        }
    }
}
